import pymysql
import pandas as pd
import re
df=pd.read_csv("数据预处理\清洗后的数据.csv")
Ccode=df["行业代码"]
Cshares=df["行业名称"]
code=df["股票代码"]
name=df["公司名称"]
price=df["当前价"]
Iad=df["涨跌额"]
Uadr=df["涨跌幅"]
YTD=df["年初至今"]
Turnover=df["成交量"]
money=df["成交额"]
Trate=df["换手率"]
dividend_yield=df["股息率"]
market_value=df["市值"]


conn = pymysql.connect(
    user = 'root',
    passwd = '123456',
    db = 'bs',
    host = '127.0.0.1',
    port = 3306,
    charset = 'utf8',
    autocommit = True  # 自动提交确认
)
k=0
l=0
cursor = conn.cursor() 
for c in range(df.shape[0]):
    sql_1 ="""INSERT INTO DATA(`Ccode`,`Cshares`,`code`,`name`,`price`,`Iad`,`Uadr`,`YTD`,`Turnover`,`money`,`Trate`,`dividend_yield`,`market_value`)
            VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)
            """
    try:
        # 执行sql语句
            cursor.execute(sql_1,(str(Ccode[c]),str(Cshares[c]),str(code[c]),str(name[c]),float(price[c]),float(Iad[c]),
                                  float(Uadr[c]),float(YTD[c]),float(Turnover[c]),float(money[c]),float(Trate[c]),float(dividend_yield[c]),float(market_value[c])))
        # 执行sql语句
            conn.commit()
            print("success!")
            k=k+1
    except:
            l=l+1
            print("Error!", c, cursor._last_executed)
        # 发生错误时回滚
            conn.rollback()
sql="""SELECT COUNT(*) FROM DATA"""
cursor.execute(sql)
m=str(cursor.fetchall())
m=re.findall("\d",m)
m=str(m)
m=m.replace("'",'')
m=m.replace("[",'')
m=m.replace("]",'')
m=m.replace(",",'')
m=m.replace(" ",'')
m=int(m)
print("共插入{}条数据,有{}条数据插入失败".format(k,l))
print("已有{}条数据".format(m))
conn.close()
